﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;

namespace ONTS.WEB.Views
{
    public partial class test_excel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string path = MapPath("../Utility/template.xlsx");
            ExportExcel(CreateContentBot(path));
        }

        public byte[] CreateContentBot(string path)
        {
            var template = new FileInfo(path);

            if (template.Exists)
            {
                template.Delete();
                template = new FileInfo(path);
            }

            using (ExcelPackage package = new ExcelPackage(template))
            {
                // Create worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("BOT Summary Sheet");

                // Add the headers
                worksheet.Cells[1, 1].Value = "ลำดับที่";
                worksheet.Cells[1, 2].Value = "ขั้นตอนของงาน";
                worksheet.Cells[1, 3].Value = "งานที่ส่งมอบ";
                worksheet.Cells[1, 4].Value = "สถานะของงาน";
                worksheet.Cells[1, 5].Value = "วันที่แล้วเสร็จ";
                worksheet.Cells[1, 6].Value = "หมายเหตุ";
                // Add row data
                worksheet.Cells["A2"].Value = 1;
                worksheet.Cells["B2"].Value = "ทดสอบ";
                worksheet.Cells["C2"].Value = "ยังไม่ส่งมีไรป่ะ";
                worksheet.Cells["D2"].Value = "ยังไม่เสร็จ";
                worksheet.Cells["E2"].Value = "ยังไม่รู้";
                worksheet.Cells["F2"].Value = "บอกไม่ได้";

                // Add picture
                string imgpath = MapPath("../images/Co9_logo.jpg");
                Bitmap image = new Bitmap(imgpath);
                ExcelPicture excelImage = null;
                if (image != null)
                {
                    excelImage = worksheet.Drawings.AddPicture("AddImage", image);
                    excelImage.From.Column = 11;
                    excelImage.From.Row = 3;
                    excelImage.SetSize(115, 64);
                    //// 2x2 px space for better alignment
                    excelImage.From.ColumnOff = Pixel2MTU(2);
                    excelImage.From.RowOff = Pixel2MTU(2);
                }

                //worksheet.Cells.AutoFitColumns(0);
                // set header text
                //worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" BOT Summary Sheet";

                //worksheet.View.PageLayoutView = true;

                // Setting excel properties
                package.Workbook.Properties.Title = "Excel Export";
                package.Workbook.Properties.Author = "noo";
                package.Workbook.Properties.Created = DateTime.Now;

                return package.GetAsByteArray();
            }
        }

        private void ExportExcel(byte[] pByte)
        {
            try
            {            
                //export excel
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=Export_Exam.xls");
                Response.BinaryWrite(pByte);
                Response.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath)
        {
            Bitmap image = new Bitmap(imagePath);
            ExcelPicture excelImage = null;
            if (image != null)
            {
                excelImage = oSheet.Drawings.AddPicture("AddImage", image);
                excelImage.From.Column = colIndex;
                excelImage.From.Row = rowIndex;
                excelImage.SetSize(100, 100);
                // 2x2 px space for better alignment
                excelImage.From.ColumnOff = Pixel2MTU(2);
                excelImage.From.RowOff = Pixel2MTU(2);
            }
        }

        public int Pixel2MTU(int pixels)
        {
            int mtus = pixels * 9525;
            return mtus;
        }

      }
}